/*
BEGIN TRANSACTION 
EXEC sp_update_release_mrb_status 4746, 3, 'Release', 'no comments pls'
select * from engineering_lots where lot_id = 4746 and wafer_id = 3
select * from informative_binning where lot_id = 4746 order by cast(wafer_id as int)
select * from sort_bin_release_table where lot_id = 4746 order by cast(wafer_id as int)
select * from mrb_release_lots  where lot_id = 4746 order by cast(wafer_id as int)
ROLLBACK
*/

/*
CREATED BY	:	MURALI KRISHNA YENGALA
CREATED ON	:	7TH JULY 2005
*/

ALTER PROCEDURE sp_update_release_mrb_status

@LOT_ID 	INT,
@WAFER_ID	INT,	
@MOVE_TO	VARCHAR(50),
@COMMENTS 	VARCHAR(250)

AS
BEGIN
	DECLARE @MRB_LIMIT_STATUS INT
	DECLARE @RMA_LIMIT_STATUS INT
	DECLARE @BIN_NUMBERS VARCHAR(1000)
	DECLARE @PROCESSSTEP_ID INT	
	DECLARE @BIN_INFO VARCHAR(1000)
	DECLARE @SP_CHAR_IDX INT
	DECLARE @IB_BIN_QUANTITIES VARCHAR(1000)
	DECLARE @LOOP_VAR INT
	DECLARE @GOOD_BINS_COUNT INT
	DECLARE @SQL_QUERY NVARCHAR(1000)
	DECLARE @TMP_STR VARCHAR(1000)
	DECLARE @BIN_NUMBER VARCHAR(50)
	

	IF @MOVE_TO = 'Release'
	BEGIN
		SET @MRB_LIMIT_STATUS = 1
		SET @RMA_LIMIT_STATUS = 1
	END
	ELSE 
	BEGIN
		SET @MRB_LIMIT_STATUS = 1
		/*
			MRB_LIMIT AND RMA_LIMIT FLAGS ARE SET TO 1, when a wafer is partially scrapped.
			If the wafer is not partially scrapped, MRB_LIMIT is set to 1 and RMA_LIMIT is set to 0
		*/
		IF( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ARLT.LOT_ID = @LOT_ID AND ARLT.WAFER_ID = @WAFER_ID) > 0) 
		BEGIN
			SET @RMA_LIMIT_STATUS = 1
		END
		ELSE
		BEGIN
			SET @RMA_LIMIT_STATUS = 0		
		END
	END	

	UPDATE INFORMATIVE_BINNING SET MRB_LIMIT = @MRB_LIMIT_STATUS, RMA_LIMIT = @RMA_LIMIT_STATUS WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID 

	--If a wafer that is partially released to Assembly is scrapped/released the following code will execute.
	IF( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ARLT.LOT_ID = @LOT_ID AND ARLT.WAFER_ID = @WAFER_ID) > 0) 
	BEGIN
		SELECT DISTINCT @PROCESSSTEP_ID = PROCESSSTEP_ID FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
		--@BIN_INFO will have bins released to assembly delimited by '&'
	    	SET @BIN_INFO = DBO.fn_get_bins_used_for_assembly_release(@LOT_ID, @WAFER_ID)
		SET @IB_BIN_QUANTITIES = ''
		SET @LOOP_VAR = 1
		select @GOOD_BINS_COUNT = count(*) from wip_Step_bin_table where WSBT_Bin_Type = 'ACCEPT' and processStep_id = @PROCESSSTEP_ID
		WHILE( @LOOP_VAR <= @GOOD_BINS_COUNT )
		BEGIN
			IF( CHARINDEX( '&'+CAST(@LOOP_VAR AS VARCHAR)+'&', @BIN_INFO ) = 0 )
			BEGIN
				SET @IB_BIN_QUANTITIES = @IB_BIN_QUANTITIES + 'BIN_'+CAST(@LOOP_VAR AS VARCHAR)+','
			END				
			SET @LOOP_VAR = @LOOP_VAR + 1
		END
		IF( LEN(@IB_BIN_QUANTITIES ) > 0 )
		BEGIN
			SET @BIN_NUMBERS = @IB_BIN_QUANTITIES
		END
		/*
		If the wafer is scrapped (ie., moved to RTV, Matrix, Scrap, Engg ) the following modifications are made.
			1. Insert the wafer details in Engineering_lots. This wafer will have valid data only for bins that are scrapped only.
			2. Update the sort_bin_release_table by setting bin quantities and bin available quantities to 0 for the bins that are scrapped.
		
		If the wafer is released. The following modifications are made.
			1. insert the wafer details in MRB_RELEASE_LOTS.
			2. update the sort_bin_release_lot_table's balance bin quantities with bin quantities	
		*/

		IF @MOVE_TO <> 'Release'
		BEGIN
			SET @TMP_STR = REPLACE( @BIN_NUMBERS, ',' ,'+' )
			SET @TMP_STR = SUBSTRING( @TMP_STR, 0, LEN(@TMP_STR))
			SET @SQL_QUERY = 'INSERT INTO ENGINEERING_LOTS(Lot_ID, Wafer_ID, Test_Time, Good_Dice, Yield,'+@BIN_NUMBERS+'Defect_Density, Bin_Qty, Bin_Number, Bin_Rejected, MRB_LIMIT, RMA_LIMIT, MOVED_TO, COMMENTS, TIMESTAMP) SELECT Lot_ID, Wafer_ID, Test_Time, '+@TMP_STR+', Yield,'+@BIN_NUMBERS+'Defect_Density, Bin_Qty, Bin_Number, Bin_Rejected, MRB_LIMIT, RMA_LIMIT, '''+@MOVE_TO+''', '''+@COMMENTS+''', CURRENT_TIMESTAMP FROM INFORMATIVE_BINNING WHERE LOT_ID = '+CAST(@LOT_ID AS VARCHAR)+' AND WAFER_ID = '+CAST(@WAFER_ID AS VARCHAR)
			EXECUTE SP_EXECUTESQL @SQL_QUERY
			SET @TMP_STR = REPLACE(@BIN_NUMBERS, ',', ' = 0,')
			SET @TMP_STR = SUBSTRING(@TMP_STR, 0, LEN(@TMP_STR))
			SET @SQL_QUERY = 'UPDATE SORT_BIN_RELEASE_TABLE SET '+@TMP_STR +' WHERE LOT_ID = '+CAST(@LOT_ID AS VARCHAR)+' AND WAFER_ID = '+CAST(@WAFER_ID AS VARCHAR)
			EXECUTE SP_EXECUTESQL @SQL_QUERY
		END
		ELSE
		BEGIN
			SET @TMP_STR = ''
			SET @SQL_QUERY = 'INSERT INTO MRB_Release_Lots(Lot_ID, Wafer_ID, Test_Time, Good_Dice, Yield,'+@BIN_NUMBERS+'Defect_Density, Bin_Qty, Bin_Number, Bin_Rejected, MRB_LIMIT, RMA_LIMIT, MOVED_TO, COMMENTS, TIMESTAMP) SELECT Lot_ID, Wafer_ID, Test_Time, Good_Dice, Yield,'+@BIN_NUMBERS+'Defect_Density, Bin_Qty, Bin_Number, Bin_Rejected, MRB_LIMIT, RMA_LIMIT, '''+@MOVE_TO+''','''+@COMMENTS+''', CURRENT_TIMESTAMP FROM INFORMATIVE_BINNING WHERE LOT_ID = '+CAST(@LOT_ID AS VARCHAR)+' AND WAFER_ID = '+CAST(@WAFER_ID AS VARCHAR)
			EXECUTE SP_EXECUTESQL @SQL_QUERY
			SET @SP_CHAR_IDX = CHARINDEX(',', @BIN_NUMBERS )
			WHILE( @SP_CHAR_IDX <> 0 )
			BEGIN
				SET @BIN_NUMBER = SUBSTRING( @BIN_NUMBERS, 1, (@SP_CHAR_IDX-1))
				SET @TMP_STR = @TMP_STR+@BIN_NUMBER+'_BALANCE_QTY = '+@BIN_NUMBER+','
				SET @BIN_NUMBERS = SUBSTRING( @BIN_NUMBERS, @SP_CHAR_IDX+1, LEN(@BIN_NUMBERS))
				SET @SP_CHAR_IDX = CHARINDEX(',', @BIN_NUMBERS )
			END
			set @TMP_STR = SUBSTRING( @TMP_STR, 0, LEN(@TMP_STR))
			SET @SQL_QUERY = 'UPDATE SORT_BIN_RELEASE_TABLE SET '+@TMP_STR+' WHERE LOT_ID = '+CAST(@LOT_ID AS VARCHAR)+' AND WAFER_ID = '+CAST(@WAFER_ID AS VARCHAR)
			EXECUTE SP_EXECUTESQL @SQL_QUERY
		END
	END
	ELSE
	BEGIN
		IF @MOVE_TO <> 'Release'
		BEGIN
			INSERT INTO ENGINEERING_LOTS SELECT Lot_ID, Wafer_ID, Test_Time, Good_Dice, Yield,Bin_1, Bin_2, Bin_3, Bin_4, Bin_5, Bin_6, Bin_7, Bin_8, Bin_9, Bin_10, Bin_11, Bin_12, Bin_13, Bin_14, Bin_15,Defect_Density, Bin_Qty, Bin_Number, Bin_Rejected, MRB_LIMIT, RMA_LIMIT,@MOVE_TO,@COMMENTS, CURRENT_TIMESTAMP FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
		END
		ELSE
		BEGIN
			INSERT INTO MRB_Release_Lots SELECT Lot_ID, Wafer_ID, Test_Time, Good_Dice, Yield,Bin_1, Bin_2, Bin_3, Bin_4, Bin_5, Bin_6, Bin_7, Bin_8, Bin_9, Bin_10, Bin_11, Bin_12, Bin_13, Bin_14, Bin_15,Defect_Density, Bin_Qty, Bin_Number, Bin_Rejected, MRB_LIMIT, RMA_LIMIT,@MOVE_TO,@COMMENTS, CURRENT_TIMESTAMP FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
		END

	END
END


